#include <Excel.au3>

Local $sFilePath1 = "C:\temp_1\test.xlsx" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf

_ExcelSheetActivate($oExcel, "Sheet5")

$smav1 = Number(_ExcelReadCell($oExcel, 3, 6))
$rowIndex = 6
$changeColIndex = 9
$directionColIndex = 8
$tfexCost = 1000
$initInvest = Number(_ExcelReadCell($oExcel, 1, 12))
$runningBal = $initInvest


$sCellValue = _ExcelReadCell($oExcel, $rowIndex, 5)

$i = $rowIndex

$prevValue = 0
$currValue = 0

$rowIndexOfPrevNoOfContract = 5

While $sCellValue <> ""
   
   If $i < $smav1 + $rowIndex - 1 Then
	  _ExcelWriteCell($oExcel, "", $i, 6)
   Else
	  Local $startIndex = $i - $smav1 + 1
	  _ExcelWriteFormula($oExcel, "=Average(R" & $startIndex & "C5:R" & $i & "C5)", $i, 6)
	  
	  ;Sleep(125)
	  
	  $changeColVal = _ExcelReadCell($oExcel, $i, $changeColIndex)
	  ConsoleWrite($changeColVal)
	  
	  If $changeColVal = "CHANGE" Then
		 
		 $currValue = _ExcelReadCell($oExcel, $i, 5)
		 
		 ConsoleWrite(":$prevValue=" & $prevValue)
	  
		 If $prevValue <> 0 Then
			
			$direction = _ExcelReadCell($oExcel, $i, $directionColIndex)
			
			ConsoleWrite(":$direction=" & $direction & ":")
	  
			Local $diff
			
			If $direction = "BELOW" Then
			   $diff = $currValue - $prevValue
			Else
			   $diff = $prevValue - $currValue
			EndIf
			
			$profitLoss = $diff * $tfexCost
			
			_ExcelWriteCell($oExcel, $profitLoss, $i, 11)
			
			$nextNoOfContract = Ceiling($runningBal / ($currValue * $tfexCost) * 2)
			
			_ExcelWriteCell($oExcel, $nextNoOfContract, $i, 12)

			
			$lastNoOfContract = _ExcelReadCell($oExcel, $rowIndexOfPrevNoOfContract, 12)
			If $lastNoOfContract = "" Then
			   $lastNoOfContract = 1
			Else
			   
			   If $direction = "BELOW" Then
				  $diff = $currValue - $prevValue
				  _ExcelWriteFormula($oExcel, "=Min(R" & $rowIndexOfPrevNoOfContract & "C4:R" & $i & "C4)-R" & $rowIndexOfPrevNoOfContract & "C10", $i, 14)
			   Else
				  $diff = $prevValue - $currValue
				  _ExcelWriteFormula($oExcel, "=R" & $rowIndexOfPrevNoOfContract & "C10-Max(R" & $rowIndexOfPrevNoOfContract & "C3:R" & $i & "C3)", $i, 14)
			   EndIf
			
			EndIf
			
			$rowIndexOfPrevNoOfContract = $i
			
			$runningBal = $runningBal + ($profitLoss * $lastNoOfContract)
			
			_ExcelWriteCell($oExcel, $runningBal, $i, 13)
			
		 EndIf
	  
		 $prevValue = $currValue
		 
	  EndIf
	  
   EndIf
   
   $i = $i + 1
   $sCellValue = _ExcelReadCell($oExcel, $i, 5)
   ConsoleWrite("i=" & $i & ", " & "--" & $sCellValue & "--" & @CRLF)
   
WEnd

_ExcelWriteCell($oExcel, $runningBal, 2, 12)
   
ConsoleWrite("===DONE===" & @CRLF)
